import sqlite3
import logging
import os

# 配置日志记录
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# 定义数据库文件存储目录
DB_DIR = 'database'
if not os.path.exists(DB_DIR):
    os.makedirs(DB_DIR)

class DatabaseManager:
    def __init__(self, db_name):
        db_path = os.path.join(DB_DIR, db_name)
        self.conn = sqlite3.connect(db_path)
        self.cursor = self.conn.cursor()
        self.create_tables()

    def create_tables(self):
        # 创建随礼账本表
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS send_ledger (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                amount REAL,
                date TEXT,
                relationship TEXT,
                occasion TEXT,
                notes TEXT
            )
        ''')
        # 创建收礼账本表
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS receive_ledger (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                amount REAL,
                date TEXT,
                relationship TEXT,
                occasion TEXT,
                notes TEXT
            )
        ''')
        self.conn.commit()
        logging.info("Tables created successfully.")

    def add_record(self, ledger_type, data):
        table_name = f"{ledger_type}_ledger"
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['?' for _ in data.values()])
        query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        try:
            self.cursor.execute(query, tuple(data.values()))
            self.conn.commit()
            record_id = self.cursor.lastrowid
            logging.info(f"Added a new record with ID {record_id} to {table_name}")
            return record_id
        except Exception as e:
            logging.error(f"Failed to add record to {table_name}: {e}")
            return None

    def delete_record(self, ledger_type, record_id):
        table_name = f"{ledger_type}_ledger"
        query = f"DELETE FROM {table_name} WHERE id = ?"
        try:
            self.cursor.execute(query, (record_id,))
            self.conn.commit()
            logging.info(f"Deleted record with ID {record_id} from {table_name}")
        except Exception as e:
            logging.error(f"Failed to delete record from {table_name}: {e}")

    def update_record(self, ledger_type, record_id, new_data):
        table_name = f"{ledger_type}_ledger"
        set_clause = ', '.join([f"{key} = ?" for key in new_data.keys()])
        query = f"UPDATE {table_name} SET {set_clause} WHERE id = ?"
        values = tuple(new_data.values()) + (record_id,)
        try:
            self.cursor.execute(query, values)
            self.conn.commit()
            if self.cursor.rowcount > 0:
                logging.info(f"Updated record with ID {record_id} in {table_name}")
                return True
            return False
        except Exception as e:
            logging.error(f"Failed to update record in {table_name}: {e}")
            return False

    def get_records(self, ledger_type):
        table_name = f"{ledger_type}_ledger"
        query = f"SELECT * FROM {table_name}"
        try:
            self.cursor.execute(query)
            records = []
            columns = [description[0] for description in self.cursor.description]
            for row in self.cursor.fetchall():
                record = dict(zip(columns, row))
                records.append(record)
            logging.info(f"Retrieved {len(records)} records from {table_name}")
            return records
        except Exception as e:
            logging.error(f"Failed to retrieve records from {table_name}: {e}")
            return []

    def close(self):
        self.conn.close()
        logging.info("Database connection closed.")